package com.teradata.tool.db;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

import com.teradata.tool.xml.bean.DBConfigurationBean;
import com.teradata.tool.xml.util.XmlUtil;

/**
 * 数据库工具类
 * 
 * @author liershuai
 * 
 */
public final class DbUtil {
	private final static String QUERY_COLUMN_INFO = "SELECT columnname ,ColumnId-1024   ,CASE    WHEN Columntype IN ('I','I1','I2')THEN 'INTEGER'  WHEN Columntype = 'DA' THEN 'DATE'   WHEN Columntype = 'TS' THEN 'TIMESTAMP'  WHEN Columntype = 'F'  THEN 'FLOAT'  WHEN Columntype = 'D' THEN 'DECIMAL' WHEN Columntype IN ('CV','CF')  THEN 'CHAR'   ELSE Columntype  END Columntype  ,CASE       WHEN Columntype IN ('I','I1','I2')THEN 11       WHEN Columntype = 'DA' THEN 10       WHEN Columntype = 'TS' THEN 19       WHEN Columntype = 'F'  THEN 38       WHEN Columntype = 'D' THEN DecimalTotalDigits       WHEN Columntype IN ('CV','CF')  THEN ColumnLength       ELSE ColumnLength  END ColumnLength  ,COALESCE(columntitle,commentstring,columnname)FROM DBC.COLUMNSV WHERE Databasename=? AND   TABLENAME=? ORDER BY TABLENAME, Columnid";
	private final static String QUERY_COLUMN_NAME = "SELECT columnname  FROM DBC.COLUMNSV WHERE Databasename=? AND   TABLENAME=? ORDER BY Columnid";
	private final static String CREATE_L = "CREATE MULTISET TABLE  #DB.#TABLE#l AS #DB.#TABLE WITH NO DATA NO PRIMARY INDEX";
	private final static String DROP_L = "DROP TABLE #DB.#TABLE#l";
	private final static String BACK_L = "INSERT INTO #DB.#TABLE SELECT * FROM #DB.#TABLE#l";
	
	
	public static Boolean preLoad(Connection conn,String dbTabName) {
		PreparedStatement pstmt ;
		try {
			pstmt = conn.prepareStatement(DROP_L.replaceAll("#DB.#TABLE",dbTabName));
			pstmt.execute();
		} catch (SQLException e) {
			System.out.println("don't worry");
		}
		try {
			System.out.println(CREATE_L.replaceAll("#DB.#TABLE",dbTabName));
			pstmt = conn.prepareStatement(CREATE_L.replaceAll("#DB.#TABLE",dbTabName));
			pstmt.execute();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return true;
		
	}
	
	public static Boolean tdLoad(Connection conn,String dbTabName) throws SQLException{
		System.out.println(BACK_L.replaceAll("#DB.#TABLE",dbTabName));
		PreparedStatement pstmt = conn.prepareStatement(BACK_L.replaceAll("#DB.#TABLE",dbTabName));
		pstmt.execute();
		return true;
		
	}
	// 获取整个Bean数据
	public static DBConfigurationBean getDbConfigurationInfo() {
		// 定义数据库配置的Bean
		DBConfigurationBean dbConfigurationBean = new DBConfigurationBean();
		// 使用dbConfigurationBean初始化XMLUtil
		XmlUtil xmlUtil = new XmlUtil(dbConfigurationBean);
		// 读取第一个节点的数据(数据库配置信息)
		xmlUtil.getIndexOfBean(0);
		dbConfigurationBean.getTagAndValue();
		return dbConfigurationBean;
	}

	/**
	 * 获取一张表的建表语句
	 * 
	 * @param conn
	 * @param tableName
	 * @param db
	 * @return
	 * @throws Exception
	 */
	public static String getDdl(Connection conn, String tableName, String db)
			throws Exception {
		String ret = "";
		try {
			if (db.toUpperCase().equals("TERADATA")) {
				PreparedStatement pstmt = conn.prepareStatement("SHOW TABLE "
						+ tableName + ";");
				ResultSet rs = pstmt.executeQuery();
				if (rs.next()) {
					ret = rs.getString(1);
				}
				if (rs != null)
					rs.close();
				if (pstmt != null)
					pstmt.close();
			}
			if (ret != null && !ret.equals(""))
				// 回车符替换成换行符
				ret = ret.replace("\r", "\n");
		} catch (Exception e) {
			e.printStackTrace();
			return ret;
		}
		return ret;
	}

	public static List<String> getColumnList(Connection conn, String dbName,String tabName)
			throws SQLException {

		PreparedStatement pstmt = conn.prepareStatement(QUERY_COLUMN_NAME);
		pstmt.setString(1, dbName);
		pstmt.setString(2, tabName);
		ResultSet rs = pstmt.executeQuery();
		ArrayList<String> list = new ArrayList<String>();
		while (rs.next()) {
				list.add(rs.getString(1));
		}
		return list;
	}

	/**
	 * 获取一张表的字段名，字段序号,字段类型，字段长度和字段中文名
	 * 
	 * @param conn
	 * @param tableName
	 * @param datebaseName
	 * @return
	 * @throws Exception
	 */
	public static HashMap<String, String[]> getColumnInfo(Connection conn,
			String tableName, String databaseName) throws SQLException {
		HashMap<String, String[]> hashMap = new HashMap<String, String[]>();

		PreparedStatement pstmt = conn.prepareStatement(QUERY_COLUMN_INFO);
		pstmt.setString(1, databaseName);
		pstmt.setString(2, tableName);
		ResultSet rs = pstmt.executeQuery();
		while (rs.next()) {
			String[] infos = { rs.getString(2)// 字段序号
					, rs.getString(3)// 字段类型
					, rs.getString(4)// 字段长度
					, rs.getString(5) // 字段中文名称
			};
			hashMap.put(rs.getString(1), infos);
		}
		return hashMap;
	}

	/**
	 * 清空表
	 * 
	 * @param conn
	 * @param tableName
	 * @param databaseName
	 * @return
	 * @throws Exception
	 */
	public static boolean clearTable(Connection conn, String dbName,
			String tableName) throws SQLException {
		Statement stmt = conn.createStatement();
		stmt.executeUpdate("DELETE FROM " + tableName + " ALL");
		return true;
	}

	/**
	 * 清空表
	 * 
	 * @param conn
	 * @param tableName
	 * @param databaseName
	 * @return
	 * @throws Exception
	 */
	public static boolean clearTable(Connection conn, String dbName,
			String tableName, String whereClause) throws SQLException {
		PreparedStatement pstmt = conn.prepareStatement("DELETE FROM " + dbName
				+ "." + tableName + " " + whereClause);
		return pstmt.execute();
	}

	public static void runManipulationSql(Connection conn, List<String> list)
			throws Exception {
		int size = list.size();
		if (size == 0)
			return;
		PreparedStatement pstmt = null;
		for (int i = 0; i < size; i++) {
			pstmt = conn.prepareStatement(list.get(i));
			pstmt.addBatch();
			if ((i + 1) % 100 == 0) {
				pstmt.executeBatch();
				System.out.println("已经插入" + (i + 1) + "条记录...");
			}
		}
		pstmt.executeBatch();
		System.out.println("已经插入" + size + "条记录...");
		System.out.println("所有记录插入完毕!");
	}

	public static void main(String[] args) {
		try {
			
			Connection conn = DBConn.getInstance("db1").getSession();
			DbUtil.preLoad(conn, "S.T1");
			System.out.println(DbUtil.getDdl(conn, "S.t1", "TERADATA"));
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
}
